package edu.wfmanage.models;

import java.sql.Date;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.util.DB;


public class Ticket {

	private int id;
	private String summary;
	private String category;
	private String device;
	private String os;
	private Date date;
	private String priority;
	private String cust_name;
	private String email;
	private String dept;
	private String office;
	private String phone;
	private String desc;
	private String comments;
	private int status_id;
	
	private String support_level;
	private ArrayList<String> names;
	
		
	public ArrayList<String> getNames() {
		return names;
	}
	public void setNames(ArrayList<String> names) {
		this.names = names;
	}
	public String getSupport_level() {
		return support_level;
	}
	public void setSupport_level(String support_level) {
		this.support_level = support_level;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getDevice() {
		return device;
	}
	public void setDevice(String device) {
		this.device = device;
	}
	public String getOs() {
		return os;
	}
	public void setOs(String os) {
		this.os = os;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public String getPriority() {
		return priority;
	}
	public void setPriority(String priority) {
		this.priority = priority;
	}
	public String getCust_name() {
		return cust_name;
	}
	public void setCust_name(String cust_name) {
		this.cust_name = cust_name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getDept() {
		return dept;
	}
	public void setDept(String dept) {
		this.dept = dept;
	}
	public String getOffice() {
		return office;
	}
	public void setOffice(String office) {
		this.office = office;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getDesc() {
		return desc;
	}
	public void setDesc(String desc) {
		this.desc = desc;
	}
	public String getComments() {
		return comments;
	}
	public void setComments(String comments) {
		this.comments = comments;
	}
	
	public int getStatus_id() {
		return status_id;
	}
	public void setStatus_id(int status_id) {
		this.status_id = status_id;
	}
	
	public static String findNextSupportLevel(int status){
		
		System.out.println("\nInside Ticket.findNextSupportLevel function...\n");
		if(status==1||status==2||status==3)
			status++;
		String query = "select role.role_name as role_name from role, assignment where role.role_id=assignment.support_id and assignment.status_id = "
				+ status + ";";
		String nextSupport = "";
		ResultSet result = null;
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		try{
			while(result.next()){
				nextSupport = result.getString("role_name");
			}
		}
		catch(SQLException ex){
			ex.printStackTrace();
		}
		
		return nextSupport;
	}
	
	public static int updateStatus(int ticket_id, int status){
		
		System.out.println("\nInside Ticket.updateStatus function...\n");
        String updateQuery = "update ticket set status_id = " + status + " where ticket_id = " + ticket_id + " ;";
		
		return DB.update(updateQuery);
	}
	
	public static int updateComments(int ticket_id, String comments){
		
		System.out.println("\nInside Ticket.updateComments function...\n");
        String updateQuery = "update ticket set comments = '" + comments + "' where ticket_id = " + ticket_id + " ;";
		
		return DB.update(updateQuery);
		
	}
	
	public int updateStatusbyAdmin(String status_name, int t_id, String support_levelname){
		
		System.out.println("\nInside Ticket.updateStatusbyAdmin function...\n");
		String query1 = "select status_id from status where status_name= '" + status_name + "';";
		int status_id;
		Connection connection = DB.getConnection();
		ResultSet resultSet = DB.readFromDB(query1, connection);
		try {
			while (resultSet.next()) {
				status_id = resultSet.getInt("status_id");
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}
		int support_id=0;
		String query2 = "select role_id from role where role_name = '" + support_levelname + "';";
		resultSet = DB.readFromDB(query2, connection);
		try {
			while (resultSet.next()) {
				support_id = resultSet.getInt("role_id");
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}
		int newStatus_id = 0;
		String query3 = "select status_id from assignment where support_id = " + support_id + ";";
		resultSet = DB.readFromDB(query3, connection);
		try {
			while (resultSet.next()) {
				newStatus_id = resultSet.getInt("status_id");
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}
		
		String updateQuery = "update ticket set status_id = " + newStatus_id + " where ticket_id = " + t_id + " ;";
		
		return DB.update(updateQuery);
	}
	
	public int insert() {
		
		String insertSQL = "insert into ticket "
				+ "(user_name, phone, email, dept, office, problem_category, device, os, priority, ticket_date, summary, description, comments, status_id) "
				+ "values('" + cust_name + "', '" + phone + "', '" + email + "', '" 
				+ dept + "', '" + office + "', '" + category + "', '" + device + "', '" 
				+ os + "', '" + priority + "', " + " curdate() " + ", '" + summary + "', '" + desc + "', '"
				+ comments + "', 1);";
		return DB.update(insertSQL);
	}
	
	public static Ticket findTicket(String selectionModifier) {
		
		ResultSet resultSet = null;
		String query = "select t.ticket_id, t.user_name, t.phone, t.email, t.dept, t.office, t.problem_category, t.device, t.os, " +
				"t.priority, t.ticket_date, t.summary, t.description, t.comments, t.status_id " +
				"from ticket t " + selectionModifier;
		Ticket ticket = new Ticket();
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				
				ticket.id = resultSet.getInt("t.ticket_id");
				ticket.cust_name = resultSet.getString("t.user_name");
				ticket.phone = resultSet.getString("t.phone");
				ticket.email = resultSet.getString("t.email");
				ticket.dept = resultSet.getString("t.dept");
				ticket.office = resultSet.getString("t.office");
				ticket.category = resultSet.getString("t.problem_category");
				ticket.device = resultSet.getString("t.device");
				ticket.os = resultSet.getString("t.os");
				ticket.priority = resultSet.getString("t.priority");
				ticket.date = resultSet.getDate("t.ticket_date");
				ticket.summary = resultSet.getString("t.summary");
				ticket.desc = resultSet.getString("t.description");
				ticket.comments = resultSet.getString("t.comments");
				ticket.status_id = resultSet.getInt("t.status_id");
				
			}
		} catch (SQLException e) {
            e.printStackTrace();
		}
		DB.close(connection);
		return ticket;
	}
	
	public static ArrayList<Ticket> find(String selectionModifier) {
		ArrayList<Ticket> selection = new ArrayList<Ticket>();
		ResultSet resultSet = null;
		String query = "select t.ticket_id, t.user_name, t.phone, t.email, t.dept, t.office, t.problem_category, t.device, t.os, " +
				"t.priority, t.ticket_date, t.summary, t.description, t.comments, t.status_id " +
				"from ticket t " + selectionModifier;
		System.out.println("herequery:"+query);
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				Ticket ticket = new Ticket();
				ticket.id = resultSet.getInt("t.ticket_id");
				ticket.cust_name = resultSet.getString("t.user_name");
				ticket.phone = resultSet.getString("t.phone");
				ticket.email = resultSet.getString("t.email");
				ticket.dept = resultSet.getString("t.dept");
				ticket.office = resultSet.getString("t.office");
				ticket.category = resultSet.getString("t.problem_category");
				ticket.device = resultSet.getString("t.device");
				ticket.os = resultSet.getString("t.os");
				ticket.priority = resultSet.getString("t.priority");
				ticket.date = resultSet.getDate("t.ticket_date");
				ticket.summary = resultSet.getString("t.summary");
				ticket.desc = resultSet.getString("t.description");
				ticket.comments = resultSet.getString("t.comments");
				ticket.status_id = resultSet.getInt("t.status_id");
				selection.add(ticket);
			}
		} catch (SQLException e) {
            e.printStackTrace();
		}
		DB.close(connection);
		return selection;
	}
	
}
